package org.fastsyncer.parser.database;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentLinkedQueue;

import org.apache.commons.lang.StringUtils;
import org.fastsyncer.common.constant.ConnectorConstant;
import org.fastsyncer.common.entity.DatabaseConfig;
import org.fastsyncer.common.entity.Mapping;
import org.fastsyncer.common.entity.MappingTask;
import org.fastsyncer.connector.common.DatabaseConstant;
import org.fastsyncer.connector.database.DatabaseConnector;
import org.fastsyncer.connector.framework.ConnectorFactory;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.fastsyncer.parser.util.ConvertUtil;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

public class DatabaseParser {

	// 实现单例模式的方法，通过方法来实现实例化
	// 声明一个私有的静态属性，使只被实例化一次
	private static final DatabaseParser instance = new DatabaseParser();

	// 声明一个私有的构造方法，使别人不能随便new对象
	// 它只被调用一次，实例存在之后就不再调用了
	private DatabaseParser() {
	}

	// 声明一个公共的静态方法，外界通过调用这个方法来得到唯一的实例
	public static DatabaseParser getInstance() {
		return instance;
	}

	private static Logger logger = LoggerFactory.getLogger(DatabaseParser.class);

	/**
	 * 解析数据源Database类型
	 * 
	 * @param mappingTask
	 * @param startPos
	 * @param endPos
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> parseSource(MappingTask mappingTask, Integer startPos, Integer endPos)
			throws Exception {
		Mapping sourceMapping = mappingTask.getSourceMapping();
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(sourceMapping.getConnector());
		// 1.获取源所有数据,例如 [{ID=123, NAME=张三11}, {ID=321, NAME=李四},...]
		// 数据源实例
		DatabaseConfig sourceConfig = (DatabaseConfig) sourceMapping.getConfig();
		JdbcTemplate sourceJdbc = conn.getJdbcTemplate(sourceConfig);
		String sql = sourceMapping.getExcuteCommond().get(ConnectorConstant.OPERTION_QUERY);
		Object[] args = null;
		String driver = sourceConfig.getDriverClassName();
		if (StringUtils.equalsIgnoreCase(DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME, driver)) {
			args = new Object[] { startPos, endPos };
		} else if (StringUtils.equalsIgnoreCase(DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME, driver)) {
			args = new Object[] { endPos, startPos };
		}

		List<Map<String, Object>> rows = sourceJdbc.queryForList(sql, args);
		
		// 释放连接
		DatabaseUtil.close(sourceJdbc);
		return rows;
	}
	
	/**
	 * 同步数据源Database类型
	 * 
	 * @param mappingTask
	 * @param rows
	 * @return
	 * @throws Exception
	 */
	public Integer[] syncTarget(MappingTask mappingTask, List<Map<String, Object>> rows, Integer[] pos, ConcurrentLinkedQueue<JSONObject> reasonQuene) throws Exception {
		Mapping targetMapping = mappingTask.getTargetMapping();
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(targetMapping.getConnector());
		// 批量条数
		int size = rows.size();
		BatchPreparedStatementSetter batchSetter = new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// 2.获取源同步字段sourcePro:[{name=ID, type=4}, {name=NAME, type=12}]
				// 获取每行中的所有字段值，例如 {ID=123, NAME=张三11}
				conn.batchRowsSetter(ps, targetMapping.getFileds(), rows.get(i));
			}

			@Override
			public int getBatchSize() {
				return size;
			}
		};
		
		// 总数和成功数
		int total = size;
		int success = 0;
		try {
			// 目标源实例
			DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
			JdbcTemplate targetJdbc = conn.getJdbcTemplate(targetConfig);
			// 执行批量同步
			String sql = targetMapping.getExcuteCommond().get(ConnectorConstant.OPERTION_INSERT);
			int[] batchUpdate = targetJdbc.batchUpdate(sql, batchSetter);
			for (int i = 0; i < size; i++) {
				// 目前在批量同步Oracle时，成功会返回-2
				success += (batchUpdate[i] == 1 || batchUpdate[i] == -2) ? 1 : 0;
			}
			
			// 释放连接
			conn.close(targetJdbc);
		} catch (Exception e) {
			try {
				// 记录错误信息
				JSONObject err = new JSONObject();
				if(pos != null && pos.length == 2){
					err.put("startPos", pos[0]);
					err.put("endPos", pos[1]);
				}
				err.put("cause", e.getCause());
				err.put("message", e.getMessage());
				reasonQuene.offer(err);
			} catch (JSONException e1) {
				logger.error("When logging an error message: Converts a JSONObject exception!");
			}
		}
		return new Integer[] { total, success };
	}

	/**
	 * 增量同步修改到目标源
	 * 
	 * @param mappingTask
	 * @param targetPrimaryKey 目标源主键
     * @param sourcePrimaryKeyValue 数据源主键值
	 * @param after 变化后的数据
	 * @return
	 * @throws Exception
	 */
	public boolean update(MappingTask mappingTask, String targetPrimaryKey, String sourcePrimaryKeyValue, JSONArray after)
			throws Exception {
		logger.info(after.toString());
        // 1.解析数据源
        Mapping sourceMapping = mappingTask.getSourceMapping();
        Mapping targetMapping = mappingTask.getTargetMapping();
        
        // 2.对数据进行转换处理
        Map<String, Object> row = ConvertUtil.convert(sourceMapping, targetMapping, after);
        List<HashMap<String, Object>> targetFileds = targetMapping.getFileds();
		
		// 数据源的主键值
		final Object sPKValue = sourcePrimaryKeyValue;
		PreparedStatementSetter setter = new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				// 获取源同步字段sourcePro:[{name=ID, type=4}, {name=NAME, type=12}]
				// 获取每行中的所有字段值，例如 {ID=123, NAME=张三11}

				// 设置值参数
				int tPKType = 12;
				int filedSize = targetFileds.size();
				for (int i = 0; i < filedSize; i++) {
					// 取出字段和对应值
					HashMap<String, Object> filed = targetFileds.get(i);
					String name = (String) filed.get("name");
					int type = Integer.parseInt(filed.get("type").toString());
					// 匹配主键类型
					tPKType = targetPrimaryKey.equals(name) ? type : tPKType;
					Object value = row.get(name);
					DatabaseUtil.preparedStatementSetter(ps, i + 1, type, value);
				}

				// 设置主键参数
				DatabaseUtil.preparedStatementSetter(ps, filedSize + 1, tPKType, sPKValue);
			}
		};

		// 执行更新
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(targetMapping.getConnector());
		DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
		JdbcTemplate jdbcTemplate = conn.getJdbcTemplate(targetConfig);

		// 获取更新语句
		String sql = targetMapping.getExcuteCommond().get(ConnectorConstant.OPERTION_UPDATE);
		int update = jdbcTemplate.update(sql, setter);
		logger.info("update:" + update);
		if (update < 1) {
			throw new Exception("Execute update failed, data or does not exist, please check the parameters, or contact the system administrator!");
		}
		
		// 释放连接
		conn.close(jdbcTemplate);
		return true;
	}

	/**
	 * 增量同步新增到目标源
	 * 
	 * @param mappingTask
	 * @param after
	 * @return
	 * @throws Exception
	 */
	public boolean insert(MappingTask mappingTask, JSONArray after) throws Exception {
		logger.info(after.toString());
		// 1.解析数据源
		Mapping sourceMapping = mappingTask.getSourceMapping();
        Mapping targetMapping = mappingTask.getTargetMapping();

        // 2.对数据进行转换处理
        Map<String, Object> row = ConvertUtil.convert(sourceMapping, targetMapping, after);
        List<HashMap<String, Object>> targetFileds = targetMapping.getFileds();
        
		// 执行新增
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(targetMapping.getConnector());
		DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
		JdbcTemplate jdbcTemplate = conn.getJdbcTemplate(targetConfig);

		PreparedStatementSetter setter = new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				// 设置值参数
				int filedSize = targetFileds.size();
				for (int i = 0; i < filedSize; i++) {
					// 取出字段和对应值
					HashMap<String, Object> filed = targetFileds.get(i);
					String name = (String) filed.get("name");
					int type = Integer.parseInt(filed.get("type").toString());
					Object value = row.get(name);
					DatabaseUtil.preparedStatementSetter(ps, i + 1, type, value);
				}
			}
		};
		
		// 获取新增语句
        String sql = targetMapping.getExcuteCommond().get(ConnectorConstant.OPERTION_INSERT);
		int insert = jdbcTemplate.update(sql, setter);
		logger.info("insert:" + insert);
		if (insert < 1) {
			throw new Exception("Execute insert failed, data or does not exist, please check the parameters, or contact the system administrator!");
		}
		// 释放连接
		conn.close(jdbcTemplate);
		return true;
	}

	/**
	 * 增量同步删除到目标源
	 * 
	 * @param mappingTask
	 * @param targetPrimaryKey
     * @param sourcePrimaryKeyValue
	 * @param before
	 * @return
	 * @throws Exception
	 */
	public boolean delete(MappingTask mappingTask, String targetPrimaryKey, String sourcePrimaryKeyValue, JSONArray before) throws Exception {
		logger.info(before.toString());
		List<Object> args = new ArrayList<Object>();
		args.add(sourcePrimaryKeyValue);

		// 获取删除语句
		Mapping targetMapping = mappingTask.getTargetMapping();
		String sql = targetMapping.getExcuteCommond().get(ConnectorConstant.OPERTION_DELETE);

		// 执行删除
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(targetMapping.getConnector());
		DatabaseConfig targetConfig = (DatabaseConfig) targetMapping.getConfig();
		JdbcTemplate jdbcTemplate = conn.getJdbcTemplate(targetConfig);
		int delete = jdbcTemplate.update(sql, args.toArray());
		logger.info("delete:" + delete);
		if (delete < 1) {
			throw new Exception("Execute delete failed, data or does not exist, please check the parameters, or contact the system administrator!");
		}
		// 释放连接
		DatabaseUtil.close(jdbcTemplate);
		return true;
	}

}
